<?php

namespace helper;

use think\facade\Db;

class DbHandler
{

    /**
     * 获取数据库表信息
     * @return mixed
     */
    static public function getTables()
    {
        $db = config('database.connections.mysql.database');
        $sql = "select TABLE_NAME as table_name,ENGINE as engine,TABLE_ROWS as table_rows," .
            "TABLE_COLLATION as table_collation,TABLE_COMMENT as table_comment " .
            "from information_schema.TABLES where information_schema.TABLES.TABLE_SCHEMA = '{$db}'";
        return Db::query($sql);
    }

    /**
     * 获取表字段详情
     * @param $table
     * @return mixed
     */
    static public function getFields($table)
    {
        $db = config('database.connections.mysql.database');
        $sql = "select COLUMN_KEY as column_key,COLUMN_NAME as column_name,DATA_TYPE as data_type," .
            "COLUMN_TYPE as column_type,COLUMN_COMMENT as column_comment,COLUMN_DEFAULT as column_default," .
            "TABLE_NAME as table_name from information_schema.columns " .
            "where table_schema ='{$db}'  and table_name = '{$table}'";
        return Db::query($sql);
    }

    /**
     * 获取表字段
     * @param $table
     * @param string $withOutFields
     * @return array
     */
    static public function getFieldNames($table, $withOutFields = '')
    {
        $fields = self::getFields($table);
        $fieldNames = array_column($fields, 'column_name');
        if (!empty($withOutFields)) {
            $fieldNames = array_values(array_diff($fieldNames, explode(',', $withOutFields)));
        }
        return $fieldNames;
    }

    /**
     * 检测表是否存在
     * @param $table
     * @return bool
     */
    static public function checkTable($table)
    {
        $sql = "show tables like '{$table}'";
        $info = Db::query($sql);
        return !empty($info);
    }

    /**
     * 检测字段是否存在，可以同时检测多个字段
     * @param $table
     * @param $fieldName //字段名用","隔开
     * @return bool
     */
    static public function checkField($table, $fieldName)
    {
        $InFieldName = str_replace(',', "','", $fieldName);
        $db = config('database.connections.mysql.database');
        $sql = "SELECT count(*) as count FROM" .
            " information_schema. COLUMNS WHERE information_schema. COLUMNS .TABLE_SCHEMA = '{$db}'" .
            " AND information_schema. COLUMNS .TABLE_NAME = '{$table}'" .
            " AND information_schema. COLUMNS .COLUMN_NAME in ('{$InFieldName}')";
        $info = Db::query($sql);
        if (empty($info)) return false;
        if ($info[0]['count'] == count(explode(',', $fieldName))) return true;
        return false;
    }

    /**
     * 获取数据表完整信息
     * @return mixed
     */
    static public function getTablesDetail()
    {
        $tables = self::getTables();
        foreach ($tables as $k => $v) {
            $tables[$k]['fields'] = self::getFields($v['table_name']);
        }
        return $tables;
    }

    /**
     * 获取随机数 含防重复筛查
     * @param $tableName
     * @param $field
     * @param int $len
     * @param string $pre
     * @param array $map
     * @return string
     */
    static public function buildRandom($tableName, $field, $len = 6, $pre="", $map = [])
    {
        do {
            $randNum = $pre.Utils::random($len, 1);
        } while (
            Db::table($tableName)->where($field, $randNum)->where($map)->count() == 1
        );
        return $randNum;
    }

}